package cn.elane.elastic.global.jdbc;

import cn.elane.elastic.global.model.NameBean;
import cn.elane.elastic.global.model.NameBeanUtil;
import cn.elane.elastic.global.util.Converter;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 整合XqlLoader到Dao.<br>
 * 实现:Dao.use(? extends String).load(? extends String).execute();<br>
 *
 * @author Chenyu
 */
public class Dao {

	private final String SELECT = "SELECT";
	private final String INSERT = "INSERT";
	private final String UPDATE = "UPDATE";
	private final String DELETE = "DELETE";

	private String alias;
	//	private Xql xql;

	//	private List<Xql> xqlList = null;

	private Connection tranConn = null;
	private Statement tranStat = null;

	public Dao() {
		this.alias = JdbcSource.getDefaultAlias();
	}

	/**
	 * 指定数据源
	 *
	 * @param alias 数据源名称
	 * @return Dao Instance
	 */
	public Dao use(String alias) {
		this.clear();
		boolean b = JdbcSource.checkAlias(alias);
		if (!b) {
			throw new RuntimeException(String.format("数据源【%s】不存在！", alias));
		}
		this.alias = alias;
		return this;
	}

	/**
	 * 使用默认数据源
	 *
	 * @return Dao Instance
	 */
	public Dao useDefault() {
		this.clear();
		this.alias = JdbcSource.getDefaultAlias();
		return this;
	}

//	/**
//	 * 指定XQL
//	 *
//	 * @param xql xql
//	 * @return Dao Instance
//	 */
//	public Dao load(Xql xql) {
//		this.xql = xql;
//		return this;
//	}
//
//	/**
//	 * 指定XQL
//	 *
//	 * @param xqlId xqlId
//	 * @return Dao Instance
//	 */
//	public Dao load(String xqlId) {
//		this.xql = loadXql(xqlId);
//		return this;
//	}
//
//	/**
//	 * 根据设定的XQL执行插入.
//	 *
//	 * @param params 参数.
//	 * @return 0/1
//	 */
//	public Integer insert(NameBean params) {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		if (params == null || params.isEmpty()) {
//			throw new RuntimeException("参数为空!");
//		}
//		this.xql.setValueBean(params);
//		return insert();
//	}
//
//	/**
//	 * 根据设定的XQL执行插入.
//	 *
//	 * @param xql xql.
//	 * @return 0/1
//	 */
//	public Integer insert(Xql xql) {
//		if (xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		this.xql = xql;
//		return insert();
//	}
//
//	/**
//	 * 根据设定的XQL执行修改.
//	 *
//	 * @param params 参数.
//	 * @return 0/1-N
//	 */
//	public Integer update(NameBean params) {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		if (params == null || params.isEmpty()) {
//			throw new RuntimeException("参数为空!");
//		}
//		this.xql.setValueBean(params);
//		return update();
//	}
//
//	/**
//	 * 根据设定的XQL执行修改.
//	 *
//	 * @param xql xql.
//	 * @return 0/1-N
//	 */
//	public Integer update(Xql xql) {
//		if (xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		this.xql = xql;
//		return update();
//	}
//
//	/**
//	 * 根据设定的XQL执行删除.
//	 *
//	 * @param params 参数.
//	 * @return 0/1-N
//	 */
//	public Integer delete(NameBean params) {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		if (params == null || params.isEmpty()) {
//			throw new RuntimeException("参数为空!");
//		}
//		this.xql.setValueBean(params);
//		return delete();
//	}
//
//	/**
//	 * 根据设定的XQL执行删除.
//	 *
//	 * @param xql xql.
//	 * @return 0/1-N
//	 */
//	public Integer delete(Xql xql) {
//		if (xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		this.xql = xql;
//		return delete();
//	}
//
//	/**
//	 * 根据设定的XQL查询一条记录.
//	 * <br>该方法将直接执行XQL,调用前需确认待执行的XQL已有valueBean.
//	 *
//	 * @return NameBean
//	 */
//	public NameBean find() {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		if (!this.SELECT.equals(this.xql.getType())) {
//			throw new RuntimeException("待执行的XQL类型错误!");
//		}
//		return findOne(this.xql.getSql());
//	}
//
//	/**
//	 * 根据设定的XQL查询一条记录.
//	 *
//	 * @param params 参数.
//	 * @return NameBean
//	 */
//	public NameBean find(NameBean params) {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		if (params == null || params.isEmpty()) {
//			throw new RuntimeException("参数为空!");
//		}
//		this.xql.setValueBean(params);
//		return find();
//	}
//
//	/**
//	 * 根据设定的XQL查询一条记录.
//	 *
//	 * @param xql xql.
//	 * @return NameBean
//	 */
//	public NameBean find(Xql xql) {
//		if (xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		this.xql = xql;
//		return find();
//	}
//
//	/**
//	 * 根据设定的XQL查询多条记录.
//	 *
//	 * @return NameBean集合
//	 */
//	public List<NameBean> findList() {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		return findMore(this.xql.getSql());
//	}
//
//	/**
//	 * 根据设定的XQL查询多条记录.
//	 *
//	 * @param params 参数.
//	 * @return NameBean集合
//	 */
//	public List<NameBean> findList(NameBean params) {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		if (params == null || params.isEmpty()) {
//			throw new RuntimeException("参数为空!");
//		}
//		this.xql.setValueBean(params);
//		return findList();
//	}
//
//	/**
//	 * 根据设定的XQL查询多条记录.
//	 *
//	 * @param xql xql.
//	 * @return NameBean集合
//	 * @throws RuntimeException XQL为空/待执行的XQL类型错误
//	 */
//	public List<NameBean> findList(Xql xql) {
//		if (xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		this.xql = xql;
//		return findList();
//	}
//
//	/**
//	 * 批量执行增删改.<br>增删改的XQL可组合.
//	 *
//	 * @return true/false
//	 * @throws RuntimeException xqlList为空/xqlList中存在错误类型的XQL
//	 */
//	public boolean executeList() {
//		if (this.xqlList == null || this.xqlList.size() == 0) {
//			throw new RuntimeException("xqlList为空!");
//		}
//		List<String> list = new ArrayList<>(this.xqlList.size());
//		for (Xql xql : this.xqlList) {
//			if (this.INSERT.equals(xql.getType()) || this.UPDATE.equals(xql.getType()) || this.DELETE.equals(xql.getType())) {
//				list.add(xql.getSql());
//				continue;
//			}
//			throw new RuntimeException("xqlList中存在错误类型的XQL!");
//		}
//		return executeList(list);
//	}

	public Integer execute(String sql, Object... obj) {
		return execute(sql, obj == null ? null : Arrays.asList(obj));
	}

	/**
	 * 执行自定义的增删改类型的SQL
	 *
	 * @param sql SQL
	 * @param obj 参数
	 * @return 受影响行数.
	 */
	public Integer execute(String sql, List<Object> obj) {
		if (sql == null || "".equals(sql.trim())) {
			throw new RuntimeException("SQL为空!");
		}
		try {
			if (obj == null || obj.size() == 0) {
				return executeSql(sql);
			}
			return executeSql(getSql(sql, obj));
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 批量操作.
	 * <br><b>暂时采用直接执行SQL方式,暂不使用预编译方式.</b>
	 * <br><b>该方法暂独立事务管理.</b>
	 *
	 * @param sql 预编译SQL,暂不使用预编译方式处理.
	 * @param obj 参数列表
	 * @return
	 */
   /*public Boolean executeList(String sql, List<List<Object>> obj) {
        if (sql == null || "".equals(sql)) {
            return null;
        }
        Connection conn = null;
        Statement st = null;
        int[] rs;
        try {
            if (obj == null || obj.size() == 0) {
                return executeSql4Boolean(sql);
            }
            conn = getConn();
            conn.setAutoCommit(false);
            st = conn.createStatement();
            for (List<Object> objects : obj) {
                st.addBatch(getSql(sql, objects));
            }
            rs = st.executeBatch();
            conn.commit();
            return rs != null && rs.length > 0;
        } catch (Exception e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ignored) {
            }
            throw new RuntimeException(e);
        } finally {
            rs = null;
            JdbcSource.close(conn, st, null);
            *//*if (this.tranConn == null) {
                JdbcSource.close(conn, st, null);
            }*//*
        }
    }*/

	/**
	 * 查询一条记录.
	 *
	 * @param sql    sql
	 * @param params 参数
	 * @return NameBean
	 * @throws RuntimeException SQL为空
	 */
	public NameBean find(String sql, List<Object> params) {
		if (sql == null || "".equals(sql.trim())) {
			throw new RuntimeException("SQL为空!");
		}
		return findOne(getSql(sql, params));
	}

	/**
	 * 查询一条记录.
	 *
	 * @param sql    sql
	 * @param params 参数
	 * @return NameBean
	 */
	public NameBean find(String sql, Object... params) {
		return find(sql, params == null ? null : Arrays.asList(params));
	}

	/**
	 * 查询多条记录.
	 *
	 * @param sql    sql
	 * @param params 参数
	 * @return List<NameBean>
	 */
	public List<NameBean> findList(String sql, List<Object> params) {
		if (sql == null || "".equals(sql.trim())) {
			throw new RuntimeException("SQL为空!");
		}
		return findMore(getSql(sql, params));
	}

	/**
	 * 查询多条记录.
	 *
	 * @param sql    sql
	 * @param params 参数
	 * @return List<NameBean>
	 */
	public List<NameBean> findList(String sql, Object... params) {
		return findList(sql, params == null ? null : Arrays.asList(params));
	}

//	/**
//	 * xqlList中加入xql
//	 *
//	 * @param xql xql
//	 * @return Dao
//	 * @throws RuntimeException XQL不能为空/XQL类型错误
//	 */
//	public Dao append(Xql xql) {
//		if (xql == null) {
//			throw new RuntimeException("XQL不能为空!");
//		}
//		String type = xql.getType();
//		if (this.INSERT.equals(type) || this.DELETE.equals(type) || this.UPDATE.equals(type)) {
//			if (this.xqlList == null) {
//				this.xqlList = new ArrayList<>();
//			}
//			this.xqlList.add(xql);
//			return this;
//		} else {
//			throw new RuntimeException("xqlId:【" + xql.getUuid() + "】类型错误!");
//		}
//	}

	/**
	 * 重置Dao数据.<br>
	 * 如果开启了事务.一定要在事务回滚/提交后执行clear.否则将回滚.
	 *
	 * @return Dao Instance.
	 */
	public Dao clear() {
		try {
			if (this.tranConn != null && !this.tranConn.isClosed()) {
				if (!this.tranConn.getAutoCommit()) {
					this.tranConn.rollback();
					this.tranConn.setAutoCommit(true);
				}
			}
			return this;
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage());
		} finally {
			JdbcSource.close(this.tranConn, this.tranStat, null);
			this.tranStat = null;
			this.tranConn = null;
			this.alias = JdbcSource.getDefaultAlias();
		}
	}

	/**
	 * 开启事务管理.
	 *
	 * @return dao
	 */
	public Dao startTran() {
		try {
			this.tranConn = getConn();
			this.tranConn.setAutoCommit(false);
			return this;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 提交事务.
	 *
	 * @return dao
	 */
	public Dao commitTran() {
		try {
			if (this.tranConn != null && !this.tranConn.isClosed() && !this.tranConn.getAutoCommit()) {
				this.tranConn.commit();
			}
			return this;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (this.tranConn != null && !this.tranConn.isClosed()) {
					this.tranConn.setAutoCommit(true);
				}
				JdbcSource.close(this.tranConn, this.tranStat, null);
			} catch (SQLException ignored) {
			}
		}
	}

	/**
	 * 回滚事务.
	 *
	 * @return dao
	 */
	public Dao rollbackTran() {
		try {
			if (this.tranConn != null && !this.tranConn.isClosed() && !this.tranConn.getAutoCommit()) {
				this.tranConn.rollback();
			}
			return this;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			try {
				if (this.tranConn != null && !this.tranConn.isClosed()) {
					this.tranConn.setAutoCommit(true);
				}
				JdbcSource.close(this.tranConn, this.tranStat, null);
			} catch (SQLException ignored) {
			}
		}
	}

	/**
	 * 根据已设置的数据源获取连接.
	 *
	 * @return Connection
	 */
	private Connection getConn() {
		Connection conn = JdbcSource.getConnection(this.alias);
		if (conn == null) {
			throw new RuntimeException("★" + this.alias + "★数据库连接获取异常!");
		}
		return conn;
	}

//	/**
//	 * 根据设定的XQL执行插入.
//	 *
//	 * @return 0/1
//	 */
//	private Integer insert() {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		if (!this.INSERT.equals(this.xql.getType())) {
//			throw new RuntimeException("待执行的XQL类型错误!");
//		}
//		return executeSql(this.xql.getSql());
//	}
//
//	/**
//	 * 根据设定的XQL执行修改.
//	 *
//	 * @return 0/1-N
//	 */
//	private Integer update() {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		if (!this.UPDATE.equals(this.xql.getType())) {
//			throw new RuntimeException("待执行的XQL类型错误!");
//		}
//		return executeSql(this.xql.getSql());
//	}
//
//	/**
//	 * 根据设定的XQL执行删除.
//	 *
//	 * @return 0/1-N
//	 */
//	private Integer delete() {
//		if (this.xql == null) {
//			throw new RuntimeException("XQL为空!");
//		}
//		if (!this.DELETE.equals(this.xql.getType())) {
//			throw new RuntimeException("待执行的XQL类型错误!");
//		}
//		return executeSql(this.xql.getSql());
//	}

	/**
	 * 批量执行SQL
	 *
	 * @param sqlList SQL集合
	 * @return boolean
	 * @throws RuntimeException 参数为空/执行异常
	 */
	private boolean executeList(List<String> sqlList) {
		if (sqlList == null || sqlList.size() == 0) {
			throw new RuntimeException("XqlList为空!");
		}
		Connection conn = null;
		Statement stat = null;
		try {
			conn = getConn();
			conn.setAutoCommit(false);
			stat = conn.createStatement();
			for (String sql : sqlList) {
				stat.addBatch(sql);
			}
			stat.executeBatch();
			conn.commit();
			return true;
		} catch (Exception e) {
			try {
				if (conn != null && !conn.isClosed() && !conn.getAutoCommit()) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			return false;
		} finally {
			JdbcSource.close(conn, stat, null);
		}
	}

	/**
	 * 执行无参SQL
	 *
	 * @param sql SQL
	 * @return 受影响行数.
	 * @throws RuntimeException SQL为空/SQL执行异常.
	 */
	private Integer executeSql(String sql) {
		if (sql == null || "".equals(sql.trim())) {
			throw new RuntimeException("SQL为空!");
		}
		System.out.println("★ " + this.alias + " ★ " + sql);
		Connection conn = null;
		Statement st = null;
		try {
			if (this.tranConn == null || this.tranConn.isClosed()) {
				conn = getConn();
				st = conn.createStatement();
				return st.executeUpdate(sql);
			} else {
				this.tranStat = this.tranConn.createStatement();
				return this.tranStat.executeUpdate(sql);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcSource.close(conn, st, null);
		}
	}

	/**
	 * 查询一条记录.
	 *
	 * @param sql 无参查询语句.
	 * @return NameBean
	 * @throws RuntimeException SQL为空/SQL执行异常
	 */
	private NameBean findOne(String sql) {
		if (sql == null || "".equals(sql.trim())) {
			throw new RuntimeException("SQL为空!");
		}
		if (!sql.trim().toUpperCase().startsWith(this.SELECT)) {
			throw new RuntimeException("SQL类型错误!");
		}
		System.out.println("★ " + this.alias + " ★ " + sql);
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				return this.getNameBean(rs);
			}
			return null;
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcSource.close(conn, st, rs);
		}
	}

	/**
	 * 查询多条记录.
	 *
	 * @param sql sql
	 * @return List<NameBean>
	 * @throws RuntimeException SQL为空/SQL执行异常
	 */
	private List<NameBean> findMore(String sql) {
		if (sql == null || "".equals(sql.trim())) {
			throw new RuntimeException("SQL为空!");
		}
		//TODO:该校验方式需整改.SELECT方式有WITH TEMP AS () SELECT * FROM TEMP
		if (!sql.trim().toUpperCase().startsWith(this.SELECT)) {
			throw new RuntimeException("SQL类型错误!");
		}
		System.out.println("★ " + this.alias + " ★ " + sql);
		List<NameBean> list;
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = getConn();
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			list = new ArrayList<>();
			while (rs.next()) {
				list.add(this.getNameBean(rs));
			}
			return list;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			list = null;
			JdbcSource.close(conn, st, rs);
		}
	}

	private NameBean getNameBean(ResultSet rs) {
		ResultSetMetaData md;
		String label;
		Object obj;
		NameBean bean = new NameBean();
		try {
			md = rs.getMetaData();
			for (int i = 0; i < md.getColumnCount(); i++) {
				label = md.getColumnLabel(i + 1);
				obj = rs.getObject(label);
				if (obj == null) {
					bean.put(label, "");
					continue;
				}
				if (obj instanceof Byte) {
					bean.put(label, rs.getByte(label));
				} else if (obj instanceof Short) {
					bean.put(label, rs.getShort(label));
				} else if (obj instanceof Integer) {
					bean.put(label, rs.getInt(label));
				} else if (obj instanceof Long) {
					bean.put(label, rs.getLong(label));
				} else if (obj instanceof Float) {
					bean.put(label, rs.getFloat(label));
				} else if (obj instanceof Double) {
					bean.put(label, rs.getDouble(label));
				} else if (obj instanceof BigDecimal) {
					bean.put(label, rs.getBigDecimal(label));
				} else if (obj instanceof Boolean) {
					bean.put(label, rs.getBoolean(label));
				} else if (obj instanceof Timestamp) {
					bean.put(label, Converter.toDateStr(rs.getTimestamp(label)));
				} else if (obj instanceof Date) {
					bean.put(label, Converter.toDateStr(rs.getDate(label)));
				} else if (obj instanceof Time) {
					bean.put(label, rs.getTime(label).toLocalTime().toString());
				} else {
					String value = rs.getString(label);
					try {
						if (NameBeanUtil.ifObjectStr(value)) {
							bean.put(label, new NameBean(value));
						} else if (NameBeanUtil.ifArrayStr(value)) {
							bean.put(label, NameBeanUtil.init2List(value));
						} else if (value.startsWith("[") && value.endsWith("]") && !value.startsWith("[{")) {
							if (value.length() > 2) {
								Object[] objRs = value.substring(1, value.length() - 1).split(",");
								bean.put(label, objRs);
							} else {
								bean.put(label, new byte[]{});
							}
						} else {
							bean.put(label, value);
						}
					} catch (Exception e) {
						bean.put(label, obj);
					}
				}
			}
			return bean;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	private String getSql(String str, List<Object> params) {
		if (str == null || "".equals(str)) {
			return null;
		}
		StringBuffer sql;
		try {
			sql = new StringBuffer(str);
			return getSql(sql, params);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			sql = null;
		}
	}

	private String getSql(String str, Object... params) {
		List<Object> list = null;
		StringBuffer sql;
		try {
			if (params != null && params.length > 0) {
				list = new ArrayList<>(Arrays.asList(params));
			}
			sql = new StringBuffer(str);
			return getSql(sql, list);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			list = null;
			sql = null;
		}
	}

	private String getSql(StringBuffer sql, List<Object> params) {
		if (params != null && params.size() > 0 && sql.indexOf("?") > 0) {
			int index;
			for (Object param : params) {
				index = sql.indexOf("?");
				if (index == -1) {
					break;
				}
				if (param instanceof Byte || param instanceof Short || param instanceof Integer || param instanceof Long || param instanceof Float || param instanceof Double || param instanceof BigDecimal || param instanceof BigInteger) {
					sql.replace(index, index + 1, param.toString());
				} else {
					sql.replace(index, index + 1, "'" + param.toString() + "'");
				}
			}
		}
		return sql.toString();
	}

}